﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Configuration;
using System.Web;

namespace ajayumi.Platform.Web.Controls.DataGrid
{
    public class SqlHelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static readonly string connectionString = ConfigurationManager.ConnectionStrings["Davidnile"].ConnectionString;

        #region ExecuteNonQuery

        /// <summary>
        /// 执行一个存储过程或T-SQL语句
        /// </summary>
        /// <param name="cmdType">SqlCommand命令类型 （存储过程，T-SQL语句，等等。）</param>
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
        /// <param name="commandParams">以数组形式提供SqlCommand命令中用到的参数列表</param>
        /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParams)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(con, cmd, cmdType, cmdText, commandParams);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }

        /// <summary>
        /// 执行一个存储过程或T-SQL语句
        /// </summary>
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
        /// <param name="commandParams">以数组形式提供SqlCommand命令中用到的参数列表</param>
        /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>
        public static int ExecuteNonQuery(string cmdText, params SqlParameter[] commandParams)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(con, cmd, CommandType.Text, cmdText, commandParams);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }

        /// <summary>
        /// 执行一个存储过程或T-SQL语句
        /// </summary>
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
        /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>
        public static int ExecuteNonQuery(string cmdText)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(cmdText, con))
                {
                    con.Open();
                    int val = cmd.ExecuteNonQuery();
                    return val;
                }
            }
        }
        public static int ExecuteNonQuery(string cmdText,int timeout)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(cmdText, con))
                {
                    cmd.CommandTimeout = timeout;
                    con.Open();
                    int val = cmd.ExecuteNonQuery();
                    return val;
                }
            }
        }
        #endregion

        #region ExecuteReader

        public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(con, cmd, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                con.Close();
                throw;
            }
        }

        #endregion

        #region ExecuteDataSet

        public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params SqlParameter[] para)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();

                using (SqlCommand cmd = new SqlCommand())
                {
                    DataSet ds = new DataSet();
                    PrepareCommand(con, cmd, cmdType, cmdText, para);
                    adapter.SelectCommand = cmd;
                    adapter.Fill(ds);

                    return ds;
                }
            }
        }



        public static DataSet ExecuteDataSet(string cmdtext)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                using (SqlCommand cmd = new SqlCommand())
                {
                    DataSet ds = new DataSet();
                    PrepareCommand(con, cmd, CommandType.Text, cmdtext, null);
                    adapter.SelectCommand = cmd;
                    adapter.Fill(ds);

                    return ds;
                }
            }
        }

        public static DataSet ExecuteDataSet(string cmdtext, params SqlParameter[] para)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                using (SqlCommand cmd = new SqlCommand())
                {
                    DataSet ds = new DataSet();
                    PrepareCommand(con, cmd, CommandType.Text, cmdtext, para);
                    adapter.SelectCommand = cmd;
                    adapter.Fill(ds);

                    return ds;
                }
            }
        }


        #endregion

        #region ExecuteScalar

        public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(con, cmd, cmdType, cmdText, commandParameters);
                    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }


        public static object ExecuteScalar(string cmdText)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(cmdText, con))
                {
                    con.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        public static object ExecuteScalar(string cmdText, params SqlParameter[] commandParameters)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(con, cmd, CommandType.Text, cmdText, commandParameters);
                    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }
        #endregion

        #region 获取指定表中指定字段的最大值
        /// <summary>
        /// 获取指定表中指定字段的最大值
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="field">字段</param>
        /// <returns>Return Type:Int</returns>
        public static int GetMaxID(string tableName, string field)
        {
            string s = "select Max({0}) from {1}";
            s = string.Format(s, field, tableName);
            int i = Convert.ToInt32(SqlHelper.ExecuteScalar(s) == DBNull.Value ? "0" : SqlHelper.ExecuteScalar(s));
            return i;
        }
        #endregion

        #region 建立SqlCommand
        /// <summary>
        /// 建立SqlCommand
        /// </summary>
        /// <param name="con">SqlConnection　对象</param>
        /// <param name="cmd">要建立的Command</param>
        /// <param name="cmdType">CommandType</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="cmdParms">参数</param>
        private static void PrepareCommand(SqlConnection con, SqlCommand cmd, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (con.State != ConnectionState.Open)
                con.Open();

            cmd.Connection = con;
            cmd.CommandType = cmdType;
            cmd.CommandText = cmdText;

            if (cmdParms != null)
                foreach (SqlParameter para in cmdParms)
                    cmd.Parameters.Add(para);
        }

        #endregion

        #region 更新某一字段的值

        /// <summary>
        /// 更新某表中某一字段的值
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="fieldName">更新的字段名</param>
        /// <param name="fieldValue">更新的字段的值</param>
        /// <param name="identityFieldName">标识字段的名称</param>
        /// <param name="identityValue">标识字段的值</param>
        public static void UpdateAfield(string tablename, string fieldName, string fieldValue, string identityFieldName, string identityValue)
        {
            string s = "Update {0} set {1}='{2}' where {3}='{4}'";
            s = string.Format(s, tablename, fieldName, fieldValue, identityFieldName, identityValue);
            ExecuteNonQuery(s);
        }

        #endregion

        #region 分页获取

        /// <summary>
        /// 分页获取数据列表 适用于SQL2005
        /// </summary>
        /// <param name="SelectList">选取字段列表</param>
        /// <param name="tablename">数据源名称表名或视图名称</param>
        /// <param name="where">筛选条件</param>
        /// <param name="OrderExpression">排序 必须指定一个排序字段</param>
        /// <param name="pageindex">页索引 从0开始</param>
        /// <param name="pagesize">每页记录数</param>
        /// <returns></returns>
        public static DataTable GetDataByPager(string SelectList, string tablename, string where, string OrderExpression, int pageindex, int pagesize)
        {
            string cmd = "GetRecordFromPage";
            SqlParameter[] para = new SqlParameter[6];
            para[0] = new SqlParameter("@SelectList", SelectList); //--欲选择字段列表
            para[1] = new SqlParameter("@TableSource", tablename);    //--表名或视图表 
            para[2] = new SqlParameter("@SearchCondition", where);    //--查询条件
            para[3] = new SqlParameter("@OrderExpression", OrderExpression);    //--排序表达式
            para[4] = new SqlParameter("@pageindex", pageindex);   //--页号,从0开始
            para[5] = new SqlParameter("@pagesize", pagesize); //--页尺寸

            return ExecuteDataSet(CommandType.StoredProcedure, cmd, para).Tables[0];
        }
        //GetRecordFromId
        public static DataTable GetDataByRow(string SelectList, string tablename, string where, string OrderExpression, int row, int pagesize)
        {
            string cmd = "GetRecordFromRow";
            SqlParameter[] para = new SqlParameter[6];
            para[0] = new SqlParameter("@SelectList", SelectList); //--欲选择字段列表
            para[1] = new SqlParameter("@TableSource", tablename);    //--表名或视图表 
            para[2] = new SqlParameter("@SearchCondition", where);    //--查询条件
            para[3] = new SqlParameter("@OrderExpression", OrderExpression);    //--排序表达式
            para[4] = new SqlParameter("@Row", row);   //--id,从0开始
            para[5] = new SqlParameter("@pagesize", pagesize); //--页尺寸

            return ExecuteDataSet(CommandType.StoredProcedure, cmd, para).Tables[0];
        }

        #endregion

        #region 获取某表中的总记录数

        /// <summary>
        /// 获取某表中的总记录数
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <returns></returns>
        public static int GetRecordCount(string tablename)
        {
            string s = "select count(*) from {0}";
            s = string.Format(s, tablename);
            return Convert.ToInt32(ExecuteScalar(s));
        }

        public static int GetRecordCount(string tablename, string where)
        {
            string s = "select count(*) from {0} where {1}";
            s = string.Format(s, tablename, where);
            return Convert.ToInt32(ExecuteScalar(s));
        }

        #endregion

        #region 根据条件获取指定表中的数据

        /// <summary>
        /// 根据条件获取指定表中的数据
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="where">条件</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string tablename, string where)
        {
            string s = "select * from " + tablename;
            if (where != "")
                s += " where " + where;

            return SqlHelper.ExecuteDataSet(s).Tables[0];
        }

        /// <summary>
        /// 根据条件获取指定表中的数据
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="where">条件</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql)
        {
            return SqlHelper.ExecuteDataSet(sql).Tables[0];
        }


        #endregion

        #region 根据ID 获取一行数据

        /// <summary>
        /// 根据主键Id,获取一行数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="keyName">主键名称</param>
        /// <param name="value">值</param>
        /// <param name="msg">返回信息</param>
        /// <returns></returns>
        public static DataRow GetADataRow(string tableName, string keyName, string value, out string msg)
        {
            try
            {
                string s = "select * from {0} where {1}='{2}'";
                s = string.Format(s, tableName, keyName, value);
                DataTable dt = ExecuteDataSet(s).Tables[0];
                if (dt.Rows.Count > 0)
                {
                    msg = "OK";
                    return dt.Rows[0];
                }
                else
                {
                    msg = "";
                    return null;
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return null;
            }
        }

        /// <summary>
        /// 根据主键Id,获取一行数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="keyName">主键名称</param>
        /// <param name="value">值</param>
        /// <param name="msg">返回信息</param>
        /// <returns></returns>
        public static string GetAString(string getName, string tableName, string keyName, string value)
        {
            try
            {
                string s = "select {0} from {1} where {2} like '{3}'";
                s = string.Format(s, getName, tableName, keyName, value);
                DataTable dt = ExecuteDataSet(s).Tables[0];
                if (dt.Rows.Count > 0)
                {
                    return dt.Rows[0][0].ToString();
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                return null;
            }
        }

        #endregion

        #region 由Object取值
        /// <summary>
        /// 取得Int值,如果为Null 则返回０
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static int GetInt(object obj)
        {
            if (obj.ToString() != "")
                return int.Parse(obj.ToString());
            else
                return 0;
        }

        /// <summary>
        /// 取得byte值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static byte Getbyte(object obj)
        {
            if (obj.ToString() != "")
                return byte.Parse(obj.ToString());
            else
                return 0;
        }

        /// <summary>
        /// 获得Long值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static long GetLong(object obj)
        {
            if (obj.ToString() != "")
                return long.Parse(obj.ToString());
            else
                return 0;
        }

        /// <summary>
        /// 取得Decimal值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static decimal GetDecimal(object obj)
        {
            if (obj.ToString() != "")
                return decimal.Parse(obj.ToString());
            else
                return 0;
        }

        /// <summary>
        /// 取得Guid值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static Guid GetGuid(object obj)
        {
            if (obj.ToString() != "")
                return new Guid(obj.ToString());
            else
                return Guid.Empty;
        }

        /// <summary>
        /// 取得DateTime值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static DateTime GetDateTime(object obj)
        {
            if (obj.ToString() != "")
                return DateTime.Parse(obj.ToString());
            else
                return DateTime.MinValue;
        }

        /// <summary>
        /// 取得bool值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static bool GetBool(object obj)
        {
            if (obj.ToString() == "1" || obj.ToString().ToLower() == "true")
                return true;
            else
                return false;
        }

        /// <summary>
        /// 取得byte[]
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static Byte[] GetByte(object obj)
        {
            if (obj.ToString() != "")
            {
                return (Byte[])obj;
            }
            else
                return null;
        }

        /// <summary>
        /// 取得string值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static string GetString(object obj)
        {
            if (obj != null && obj != DBNull.Value)
                return obj.ToString();
            else
                return "";
        }
        #endregion
    }
}
